import java.sql.*;

public class DBHelper {

    // 定义数据库驱动字段
    static final  String JDBC_DRIVER="com.mysql.jdbc.Driver";
    static final  String DB_URL = "jdbc:mysql://localhost:3306/jdbc";
    // 声明变量
    static String username = "root";
    static String pwd = "123456";
    static Connection conn = null;
    static Statement state = null;
    static ResultSet result = null;
    static PreparedStatement preparedStatement = null;

    // 连接数据库
    public static Connection getConn(){
        try {

            // 1.加载数据库驱动
            Class.forName(JDBC_DRIVER);

            // 2.使用DriverManager获取数据库连接
            System.out.println("------------------连接数据库--------------------");
            conn =  DriverManager.getConnection(DB_URL,username,pwd);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     *   查询
     * @param sql
     */
    public static void select(String sql){
        try {
            // 3.通过Connection对象实例化获取Statement对象，帮助执行sql语句
            state = conn.createStatement();
            // 4.执行语句，收取数据结果集
            result = state.executeQuery(sql);
            // 5.显示结果
            System.out.println("id\t|name\t|sex\t|email\t\t\t|birthday");
            //先将result指针向下移一个 --- 包含空数据查询，直接结束循环
            while (result.next()) {
                //result.getInt(0);  可以传入索引
                int id = result.getInt("id"); // 也可以传入数据表中对应的字段名
                String name = result.getString("name");
                String sex = result.getString("sex");
                String email = result.getString("email");
                Date birthday = result.getDate("birthday");
                System.out.println(id + "\t|" + name + "\t|" + sex + "\t\t|" + email + "\t|" + birthday);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * user_money
     */
    public static void select_money(String sql){
        try {
            // 3.通过Connection对象实例化获取Statement对象，帮助执行sql语句
            state = conn.createStatement();
            // 4.执行语句，收取数据结果集
            result = state.executeQuery(sql);
            // 5.显示结果
            System.out.println("id\t|name\t|sex\t|email\t\t\t|birthday|\tmoney");
            //先将result指针向下移一个 --- 包含空数据查询，直接结束循环
            while (result.next()) {
                //result.getInt(0);  可以传入索引
                int id = result.getInt("id"); // 也可以传入数据表中对应的字段名
                String name = result.getString("name");
                String sex = result.getString("sex");
                String email = result.getString("email");
                Date birthday = result.getDate("birthday");
                int  money = result.getInt("money");
                System.out.println(id + "\t|" + name + "\t|" + sex + "\t\t|" + email + "\t|" + birthday+"\t|" + money);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     *  更改
     * @param
     */
    public static int update(int id,String name){

        int result = 0;
        try {
            // 预处理命令
            String sql = "update user set name=? where id=? ";
            preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,id);
            result =preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;  // 返回受影响的行数

    }
    /**
     *  增加数据
     * @param name
     * @param sex
     * @param email
     * @param birthday
     */
    public static void insert(String name,String sex,String email,String birthday){

        int result = 0;
        try {
            String sql = "insert into user(name,sex,email,birthday) values (?,?,?,?)";
            preparedStatement = (PreparedStatement)conn.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,sex);
            preparedStatement.setString(3,email);
            preparedStatement.setString(4,birthday);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     *  删除
     * @param
     */
    public static void drop(String name){
        int result = 0;
        try {
            String sql = "delete from user where name=? ";
            preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
            preparedStatement.setString(1,name);
            result = preparedStatement.executeUpdate();
        } catch (Exception e){
            e.printStackTrace();
        }

    }

    /**
     *  用户之间转账
     */
    public static void money(String name1,String name2,int tmoney,boolean ismin){
        try {
            int result = 0;
            if (ismin == true){
                String sql1 = "update user_money set money=money-? where name=? and money>=?";
                preparedStatement = (PreparedStatement) conn.prepareStatement(sql1);
                preparedStatement.setInt(1,tmoney);
                preparedStatement.setString(2,name1);
                preparedStatement.setInt(3,tmoney);
                result = preparedStatement.executeUpdate();
                if (result>0){
                    System.out.println("转账成功！");
                    String sql2 = "update user_money set money=money+? where name=?";
                    preparedStatement = (PreparedStatement) conn.prepareStatement(sql2);
                    preparedStatement.setInt(1,tmoney);
                    preparedStatement.setString(2,name2);
                    result = preparedStatement.executeUpdate();
                }else{
                    System.out.println("转账失败,余额不足！");
                }
            } else{
                String sql2 = "update user_money set money=money-? where name=? and money>=?";
                preparedStatement = (PreparedStatement) conn.prepareStatement(sql2);
                preparedStatement.setInt(1,tmoney);
                preparedStatement.setString(2,name2);
                preparedStatement.setInt(3,tmoney);
                result = preparedStatement.executeUpdate();
                if (result>0){
                    String sql1 = "update user_money set money=money+? where name=?  ";
                    preparedStatement = (PreparedStatement) conn.prepareStatement(sql1);
                    preparedStatement.setInt(1,tmoney);
                    preparedStatement.setString(2,name1);
                    result = preparedStatement.executeUpdate();
                    System.out.println("转账成功！");
                }else{
                    System.out.println("转账失败,余额不足！");
                }
            }



        } catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 关闭连接释放资源
     */
    public static void release(){
        // 6.关闭
        if(conn!=null){
            try {
                conn.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (state!=null){
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (result!=null){
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



}
